--#############################################################
-- Replace string in the SQL TEXT field of the PCSVALUE table
-- Working appropriatelly and tested in various environments
-- But still use it at your own risk.
--
-- Created By Fabien Sanglier
--#############################################################

USE [ptcsdb]

GO

DECLARE 
    @TextPointer BINARY(16), 
    @TextIndex INT, 
    @oldString NVARCHAR(32), -- change to VARCHAR 
    @newString NVARCHAR(32), -- change to VARCHAR 
    @lenOldString INT, 
    @currentItemID INT,
    @currentPropertyID INT,
	@currentItemVersion INT,
    @currentPropertyVersion INT;
 
SET @oldString = N'por-pubcontent-dev.domain.com'; -- remove N 
SET @newString = N'por-pubcontent-tst.domain.com'; -- remove N 
 
IF CHARINDEX(@oldString, @newString) > 0  
BEGIN 
    PRINT 'Quitting to avoid infinite loop.'; 
END 
ELSE 
BEGIN 
    SELECT 'Before replacement:'; 
 
	SELECT [ITEMID], [ITEMVERSION], [PROPERTYID], [PROPERTYVERSION], [LONGVALUE]
	FROM [dbo].[PCSVALUESA]
	WHERE PATINDEX('%'+@oldString+'%', [LONGVALUE]) > 0;
 
    SET @lenOldString = DATALENGTH(@oldString)/2; -- remove /2 
 
    DECLARE irows CURSOR 
        LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
        SELECT 
            [ITEMID], [ITEMVERSION], [PROPERTYID], [PROPERTYVERSION]
        FROM 
            [dbo].[PCSVALUESA]
        WHERE 
            PATINDEX('%'+@oldString+'%', [LONGVALUE]) > 0; 
 
    OPEN irows; 
 
    FETCH NEXT FROM irows INTO @currentItemID,@currentItemVersion,@currentPropertyID,@currentPropertyVersion; 
 
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 
 
        SELECT 
            @TextPointer = TEXTPTR([LONGVALUE]),  
            @TextIndex = PATINDEX('%'+@oldString+'%', [LONGVALUE]) 
        FROM 
            [dbo].[PCSVALUESA]
        WHERE 
            ITEMID = @currentItemID AND PROPERTYID = @currentPropertyID
			AND ITEMVERSION = @currentItemVersion AND PROPERTYVERSION = @currentPropertyVersion
 
        WHILE 
        ( 
            SELECT 
                PATINDEX('%'+@oldString+'%', [LONGVALUE]) 
            FROM 
				[dbo].[PCSVALUESA]
			WHERE 
				ITEMID = @currentItemID AND PROPERTYID = @currentPropertyID
				AND ITEMVERSION = @currentItemVersion AND PROPERTYVERSION = @currentPropertyVersion
        ) > 0 
        BEGIN 
            SELECT 
                @TextIndex = PATINDEX('%'+@oldString+'%', [LONGVALUE])-1 
            FROM 
				[dbo].[PCSVALUESA]
			WHERE 
				ITEMID = @currentItemID AND PROPERTYID = @currentPropertyID
				AND ITEMVERSION = @currentItemVersion AND PROPERTYVERSION = @currentPropertyVersion
 
            UPDATETEXT [dbo].[PCSVALUESA].[LONGVALUE] @TextPointer @TextIndex @lenOldString @newString; 
        END 
 
        FETCH NEXT FROM irows INTO @currentItemID,@currentItemVersion,@currentPropertyID,@currentPropertyVersion;
    END 
 
    CLOSE irows; 
 
    DEALLOCATE irows; 
 
    SELECT 'After replacement: Occurence of ' + @oldString; 
 
	SELECT [ITEMID], [ITEMVERSION], [PROPERTYID], [PROPERTYVERSION], [LONGVALUE]
	FROM [dbo].[PCSVALUESA]
	WHERE PATINDEX('%'+@oldString+'%', [LONGVALUE]) > 0;

	SELECT 'After replacement: Occurence of ' + @newString; 
 
	SELECT [ITEMID], [ITEMVERSION], [PROPERTYID], [PROPERTYVERSION], [LONGVALUE]
	FROM [dbo].[PCSVALUESA]
	WHERE PATINDEX('%'+@newString+'%', [LONGVALUE]) > 0;
END

GO

SELECT 'END'